package dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dto.StudentDTO;

public class StudentDAO extends BaseDAO{
	
	public void add(StudentDTO dto){
		String sql="insert into tb_student values(xw.seq_id.nextval,?,?,?)";
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			ps.setString(1, dto.getStuName());
			ps.setString(2, dto.getStuSex());
			ps.setString(3, dto.getStuBirth());
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		    closeSource(conn, ps, rs);
		}
	}
	
	public List<StudentDTO> findAll(){
		List<StudentDTO> students=new ArrayList<StudentDTO>();
		String sql="select * from tb_student";
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				int stuId=rs.getInt("stu_id");
				String stuName=rs.getString("stu_name");
				String stuSex=rs.getString("stu_sex");
				String stuBirth=rs.getString("stu_birth");
				StudentDTO student=new StudentDTO(stuId,stuName,stuSex,stuBirth);
				students.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		    closeSource(conn, ps, rs);
		}
		return students;
	}
	
	public StudentDTO findById(int id){
		StudentDTO student=null;
		String sql="select * from tb_student where stu_id=?";
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()){
				String name=rs.getString("stu_name");
				String sex=rs.getString("stu_sex");
				String birth=rs.getString("stu_birth");
				student=new StudentDTO(id,name,sex,birth);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		    closeSource(conn, ps, rs);
		}
		return student;
	}
	
	public void update(StudentDTO dto){
		String sql="update tb_student set stu_name=?,stu_sex=?,stu_birth=? where stu_id=?";
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			ps.setString(1, dto.getStuName());
			ps.setString(2, dto.getStuSex());
			ps.setString(3, dto.getStuBirth());
			ps.setInt(4, dto.getStuId());
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		    closeSource(conn, ps, rs);
		}
	}
	
	public void delete(String []ids){
		String sql="delete from tb_student where stu_id=?";
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(String id:ids){
				ps.setInt(1, Integer.parseInt(id));
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		    closeSource(conn, ps, rs);
		}
	}
	
	public List<StudentDTO> findPart(int currentpage){
		List<StudentDTO> students=new ArrayList<StudentDTO>();
		String sql="select * from (select rownum as num,stu_id,stu_name,stu_sex,stu_birth from tb_student where rownum<="+currentpage*10+") where num>"+(currentpage-1)*10;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				int stuId=rs.getInt("stu_id");
				String stuName=rs.getString("stu_name");
				String stuSex=rs.getString("stu_sex");
				String stuBirth=rs.getString("stu_birth");
				StudentDTO student=new StudentDTO(stuId,stuName,stuSex,stuBirth);
				students.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		    closeSource(conn, ps, rs);
		}
		return students;
	}
	
	public int getCount(){
		int num=1;
		String sql="select count(*) as num from tb_student";
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()){
				num=rs.getInt("num");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		    closeSource(conn, ps, rs);
		}
		return num;
	}
	
}
